﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Follow.Utils;
using Follow.Model;
using System.Data.SqlClient;
using System.Transactions;

namespace Follow.DataAccess
{
    public class EquipeDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public EquipeDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Equipe> findAll()
        {
            IList<Equipe> equipes = new List<Equipe>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from equipe ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Equipe e = new Equipe();
                    e.IDEquipe = reader.GetInt32(0);
                    e.Nome = reader.GetString(1);
                    equipes.Add(e);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return equipes;
        }

        public IList<Equipe> findByName(string nome)
        {
            IList<Equipe> equipes = new List<Equipe>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from equipe where nome like '%" + nome + "%' order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Equipe e = new Equipe();
                    e.IDEquipe = reader.GetInt32(0);
                    e.Nome = reader.GetString(1);
                    equipes.Add(e);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return equipes;
        }

        public IList<Equipe> insert(Equipe equipe, Int32 idlogin)
        {
            IList<Equipe> equipes = new List<Equipe>();

            try
            {
                conn.Open();
                String sqlCommand = " insert into equipe (nome) values (@nome);";
                sqlCommand += " select * from equipe where idequipe=@@IDENTITY;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("nome", equipe.Nome));
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Equipe e = new Equipe();
                    e.IDEquipe = reader.GetInt32(0);
                    e.Nome = reader.GetString(1);
                    equipes.Add(e);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Insert", "Equipe");
            return equipes;
        }

        public IList<Equipe> update(Equipe equipe, Int32 idlogin)
        {
            IList<Equipe> equipes = new List<Equipe>();

            try
            {
                conn.Open();
                String sqlCommand = " update equipe set nome = @nome where idequipe = @id;";
                sqlCommand += " delete from equipefuncionario where equipe_idequipe = @id;";
                sqlCommand += " select * from equipe where idequipe = @id;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("@nome", equipe.Nome));
                cmd.Parameters.Add(new SqlParameter("@id", equipe.IDEquipe));
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Equipe e = new Equipe();
                    e.IDEquipe = reader.GetInt32(0);
                    e.Nome = reader.GetString(1);
                    equipes.Add(e);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Update", "Equipe");
            return equipes;
        }

        public void delete(Int32 id, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " delete from equipefuncionario where equipe_idequipe = @idEquipe; ";
                    sqlCommand += " delete from equipe where idequipe = @idEquipe ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("@idEquipe", id));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Delete", "Equipe");
            }
        }

        public Equipe findByID(Int32 id)
        {
            Equipe e = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from equipe where idequipe = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    e = new Equipe();
                    e.IDEquipe = reader.GetInt32(0);
                    e.Nome = reader.GetString(1);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return e;
        }
    }
}